import pandas as pd
import numpy as np
import math

def readData():
    data = pd.read_excel("2020data01.xlsx", sheet_name='Sheet1')#这一步读取的应该是hp滤波后的文件
    # 取值为1~i（和matlab对应）
    # 用columns 索引  1~2159
    # 用数字索引       0~2158
    columns = data.columns.tolist()
    list=[1,2,3]
    index=data.iloc[0,1:]
    print(index)
    data = data[4:]

    return data.iloc[:, 1:], columns,index


def initial(data):
    L = 6
    k = data.shape[1]
    rho = pd.DataFrame(data=np.zeros((k, 2)))
    return L, k, rho


def zscore(data, columns, i):
    a = data[data[columns[i]].notnull()]
    x = a[columns[i]]
    y = a[columns[1]]
    x = (x - x.mean()) / x.std()
    y = (y - y.mean()) / y.std()

    return x, y


def cross_cor(x, y, L):
    forcast=[x for x in range(-L,L+1)]

    T = len(y)
    p = pd.DataFrame(np.zeros((1, 2 * L + 1)))

    sigma_x= math.sqrt(pd.DataFrame.sum((x-x.mean())*(x-x.mean())))
    sigma_y= math.sqrt(pd.DataFrame.sum((y-y.mean())*(y-y.mean())))

    j=0

    for i in range(-L,0):
        n1=T+i
        n2=-i
        a = pd.DataFrame(y[0:n1] - y.mean())
        b = pd.DataFrame(x[n2:T] - x.mean())
        p.iloc[0,j]= np.sum(np.multiply(a,b))/(sigma_x*sigma_y)
        j=j+1


    for i in range(0,L+1):
        n1=T-i
        n2=i
        c = pd.DataFrame(x[0:n1] - x.mean())
        d = pd.DataFrame(y[n2:T] - y.mean())
        p.iloc[0, j] = np.sum(np.multiply(c, d)) / (sigma_x * sigma_y)
        j=j+1
    pnew=p.T.abs()
    index=np.argmax(pnew)
    return p.T.iloc[index],forcast[index]

def Forcast():
    data, columns, index = readData()
    L, k, rho = initial(data)
    for i in range(1, 2160):
        x, y = zscore(data, columns, i)
        maxvalue, maxindex = cross_cor(x, y, L)
        rho.iloc[i - 1, 0] = maxvalue
        rho.iloc[i - 1, 1] = maxindex

    rho_sorted = pd.DataFrame.sort_values(rho, by=[1, 0])
    index=index[rho_sorted.index.tolist()]
    index.index = rho_sorted.index
    rho_sorted.insert(0, '指标名称', index)
    rho_sorted.to_excel("res1.xlsx", index=False)



if __name__ == '__main__':
   # rho_sorted = Forcast()
    readData()
